from typing import List, Dict, Optional, Any

from sqlalchemy import text, exists, insert, or_, and_, func, case
from sqlalchemy.dialects import mysql

from app.db import local_teaching_management_sessionLocal, teacher_management_sessionLocal, szh_management_sessionLocal, \
    szh_info_sessionLocal
from app.db.model.teacherManagementModels import CourseSchedule, TeachingMaterialSubmit, TeachingScheduleWeek, \
    TeachingScheduleCourse, CourseInfo, TeacherDetail, Semester, DepartmentInfo, TeachingGroupInfo
from datetime import datetime

from app.utils.str_util import from_list_orm_to_list_dict


class ClassesQuery:
    def get_teaching_schedule_max_id(self):
        with szh_info_sessionLocal() as session:
            max_id = session.query(func.max(TeachingScheduleCourse.id)).scalar()
            return max_id + 1 if max_id is not None else 0

    def get_teaching_schedule_max_id1(self, semester: str) -> int:
        with szh_info_sessionLocal() as session:
            max_id1 = session.query(
                func.max(
                    case(
                        (TeachingScheduleCourse.semester == semester, TeachingScheduleCourse.id1),
                        else_=None
                    )
                )
            ).scalar()
            return max_id1 + 1 if max_id1 is not None else 0

    def add_teaching_schedule(self, teaching_schedule: TeachingScheduleCourse):
        with szh_info_sessionLocal() as session:
            session.add(teaching_schedule)
            session.commit()

    def update_teaching_schedule(self,
                                 id: int,
                                 semester: str = None,
                                 campus: str = None,
                                 grade: str = None,
                                 class_name: str = None,
                                 major: str = None,
                                 enrollment_method: str = None,
                                 # 周字段参数
                                 week01: str = None,
                                 week02: str = None,
                                 week03: str = None,
                                 week04: str = None,
                                 week05: str = None,
                                 week06: str = None,
                                 week07: str = None,
                                 week08: str = None,
                                 week09: str = None,
                                 week10: str = None,
                                 week11: str = None,
                                 week12: str = None,
                                 week13: str = None,
                                 week14: str = None,
                                 week15: str = None,
                                 week16: str = None,
                                 week17: str = None,
                                 week18: str = None,
                                 week19: str = None,
                                 week20: str = None,
                                 week21: str = None,
                                 week22: str = None):
        with szh_info_sessionLocal() as session:
            update_data = {}

            # 收集所有需要更新的字段
            if semester:
                update_data['semester'] = semester.strip()
            if campus:
                update_data['campus'] = campus.strip()
            if grade:
                update_data['grade'] = grade.strip()
            if class_name:
                update_data['class_name'] = class_name.strip()
            if major:
                update_data['major'] = major.strip()
            if enrollment_method:
                update_data['enrollment_method'] = enrollment_method.strip()

            # 周字段
            week_fields = {
                'week01': week01, 'week02': week02, 'week03': week03, 'week04': week04,
                'week05': week05, 'week06': week06, 'week07': week07, 'week08': week08,
                'week09': week09, 'week10': week10, 'week11': week11, 'week12': week12,
                'week13': week13, 'week14': week14, 'week15': week15, 'week16': week16,
                'week17': week17, 'week18': week18, 'week19': week19, 'week20': week20,
                'week21': week21, 'week22': week22
            }

            for field_name, value in week_fields.items():
                if value is not None:
                    update_data[field_name] = value.strip()

            # 检查是否有需要更新的字段
            if not update_data:
                # 如果没有需要更新的字段，直接返回或记录日志
                return False

            # 执行更新
            session.query(TeachingScheduleCourse).filter(TeachingScheduleCourse.id == id).update(update_data)
            session.commit()
            return True

    def delete_teaching_schedule(self, ids: List[int]):
        with szh_info_sessionLocal() as session:
            session.query(TeachingScheduleCourse).filter(TeachingScheduleCourse.id.in_(ids)).delete()
            session.commit()

    def get_teaching_schedule_info(
            self,
            id: str = None,
            semester: str = None,
            campus: str = None,
            id1: str = None,
            grade: str = None,
            class_name: str = None,
            major: str = None,
            enrollment_method: str = None,
            # 周字段参数
            week01: str = None,
            week02: str = None,
            week03: str = None,
            week04: str = None,
            week05: str = None,
            week06: str = None,
            week07: str = None,
            week08: str = None,
            week09: str = None,
            week10: str = None,
            week11: str = None,
            week12: str = None,
            week13: str = None,
            week14: str = None,
            week15: str = None,
            week16: str = None,
            week17: str = None,
            week18: str = None,
            week19: str = None,
            week20: str = None,
            week21: str = None,
            week22: str = None,
            page: int = 0,
            page_size: int = 0) -> (List[Dict], int):

        with szh_info_sessionLocal() as session:
            query = session.query(TeachingScheduleCourse)

            # 基本字段过滤
            if id:
                query = query.filter(TeachingScheduleCourse.id == int(id))
            if semester:
                # 改为模糊匹配
                query = query.filter(TeachingScheduleCourse.semester.ilike(f'%{semester}%'))
            if campus:
                # 改为模糊匹配
                query = query.filter(TeachingScheduleCourse.campus.ilike(f'%{campus}%'))
            if id1:
                query = query.filter(TeachingScheduleCourse.id1 == int(id1))
            if grade:
                # 改为模糊匹配
                query = query.filter(TeachingScheduleCourse.grade.ilike(f'%{grade.strip()}%'))
            if class_name:
                # 改为模糊匹配
                query = query.filter(TeachingScheduleCourse.class_name.ilike(f'%{class_name.strip()}%'))
            if major:
                # 改为模糊匹配
                query = query.filter(TeachingScheduleCourse.major.ilike(f'%{major.strip()}%'))
            if enrollment_method:
                # 改为模糊匹配
                query = query.filter(TeachingScheduleCourse.enrollment_method.ilike(f'%{enrollment_method.strip()}%'))

            # 周字段过滤（保持精确匹配）
            if week01:
                query = query.filter(TeachingScheduleCourse.week01 == week01)
            if week02:
                query = query.filter(TeachingScheduleCourse.week02 == week02)
            if week03:
                query = query.filter(TeachingScheduleCourse.week03 == week03)
            if week04:
                query = query.filter(TeachingScheduleCourse.week04 == week04)
            if week05:
                query = query.filter(TeachingScheduleCourse.week05 == week05)
            if week06:
                query = query.filter(TeachingScheduleCourse.week06 == week06)
            if week07:
                query = query.filter(TeachingScheduleCourse.week07 == week07)
            if week08:
                query = query.filter(TeachingScheduleCourse.week08 == week08)
            if week09:
                query = query.filter(TeachingScheduleCourse.week09 == week09)
            if week10:
                query = query.filter(TeachingScheduleCourse.week10 == week10)
            if week11:
                query = query.filter(TeachingScheduleCourse.week11 == week11)
            if week12:
                query = query.filter(TeachingScheduleCourse.week12 == week12)
            if week13:
                query = query.filter(TeachingScheduleCourse.week13 == week13)
            if week14:
                query = query.filter(TeachingScheduleCourse.week14 == week14)
            if week15:
                query = query.filter(TeachingScheduleCourse.week15 == week15)
            if week16:
                query = query.filter(TeachingScheduleCourse.week16 == week16)
            if week17:
                query = query.filter(TeachingScheduleCourse.week17 == week17)
            if week18:
                query = query.filter(TeachingScheduleCourse.week18 == week18)
            if week19:
                query = query.filter(TeachingScheduleCourse.week19 == week19)
            if week20:
                query = query.filter(TeachingScheduleCourse.week20 == week20)
            if week21:
                query = query.filter(TeachingScheduleCourse.week21 == week21)
            if week22:
                query = query.filter(TeachingScheduleCourse.week22 == week22)

            total_count = query.count()

            if page != 0 and page_size != 0:
                offset = (page - 1) * page_size
                query = query.offset(offset).limit(page_size)
            result = query.all()
            return from_list_orm_to_list_dict(result, {}), total_count

    def get_current_week(self, semester: str) -> Dict[str, Any]:
        with szh_info_sessionLocal() as session:
            record = session.query(TeachingScheduleWeek).filter(TeachingScheduleWeek.semester == semester).scalar()
            if not record:
                return {}
            return {column.name: getattr(record, column.name) for column in record.__table__.columns}


class CourseQuery:
    def get_arrangement_by_teacher(self, teacher_name: str, semester: str) -> List[Dict]:

        rename_map = {
            "teacher_id": "teacher_Id",
            "course_id": "courseId",
            "course_name": "courseName",
            "class_name": "className",
            "teaching_week": "teachingWeeks",
            "student_amount": "studentCount",
            "major": "majorCode",
            "enrollment_method": "enrollmentMethod",
            "parner_institution": "partnerInstitution",
            "course_type": "courseType",
            "assessment_method": "assessmentMethod",
            "book_department": "textbookDepartment",
            "course_department": "department",
            "class_advisor": "classAdvisor",
            "schedule1": "mondaySchedule",
            "schedule2": "tuesdaySchedule",
            "schedule3": "wednesdaySchedule",
            "schedule4": "thursdaySchedule",
            "schedule5": "fridaySchedule",
            "weekly_hour": "weeklyHours"
        }
        with szh_info_sessionLocal() as session:
            records = (
                session.query(CourseSchedule)
                .filter(CourseSchedule.instructor.like(f'%{teacher_name}%'),
                        CourseSchedule.semester == semester,
                        CourseSchedule.course_department.notin_(['学生科']),
                        or_(
                            CourseSchedule.course_department == "教务科",
                            CourseSchedule.course_department.like('%公共基础%'),
                            ~CourseSchedule.course_type.like('%C%')
                        )
                        )
                .all()
            )
            return from_list_orm_to_list_dict(records, rename_map)

    def get_course_id_by_course_name(self, course_name: str) -> List[str]:

        with szh_info_sessionLocal() as session:
            sql = text("""
                SELECT DISTINCT course_id
                FROM course_schedule
                WHERE course_name = :course_name
            """)
            result = session.execute(sql, {"course_name": course_name})
            # 取第一列的所有值
            return [row[0] for row in result.fetchall()]

    def get_course_name_by_course_id(self, course_id: str) -> str:
        with szh_info_sessionLocal() as session:
            sql = text("""
                        select distinct course_name
                        from course_schedule
                        where course_id = :course_id
                    """)
            result = session.execute(sql, {"course_id": course_id}).fetchone()
            return result

    def get_semester_by_teacher(self, teacher_name: str) -> List[str]:

        with szh_info_sessionLocal() as session:
            sql = text("""
                SELECT DISTINCT semester
                FROM course_schedule
                WHERE instructor = :teacher_name
            """)
            result = session.execute(sql, {"teacher_name": teacher_name})
            # 取第一列的所有值
            return [row[0] for row in result.fetchall()]

    def get_count_by_semester(self, semester: str) -> int:

        with szh_info_sessionLocal() as session:
            sql = text("""
                SELECT COUNT(*) 
                FROM course_schedule
                WHERE semester = :semester
            """)
            result = session.execute(sql, {"semester": semester}).scalar()
            return result if result is not None else 0

    def remove_course_arrangement(self, semester: str) -> int:

        with szh_info_sessionLocal() as session:
            sql = text("""
                DELETE FROM course_schedule
                WHERE semester = :semester
            """)
            result = session.execute(sql, {"semester": semester})
            session.commit()
            return result.rowcount  # 返回删除的行数

    def get_timetable_by_semester_and_instructor(self, semester: str, instructor: str) -> \
            List[Dict]:

        with szh_info_sessionLocal() as session:
            records = (session.query(CourseSchedule).filter(
                CourseSchedule.semester == semester,
                CourseSchedule.instructor == instructor
            ).all())
            return from_list_orm_to_list_dict(records, {})

    def get_timetable_by_semester_and_class_name(self, semester: str, class_name: str) -> List[Dict]:

        with szh_info_sessionLocal() as session:
            records = (session.query(CourseSchedule).filter(
                CourseSchedule.semester == semester,
                CourseSchedule.class_name == class_name
            ).all())
            return from_list_orm_to_list_dict(records, {})

    def is_classname_exist(self, semester: str, class_name: str) -> bool:
        with szh_info_sessionLocal() as session:
            record = session.query(CourseSchedule).filter(
                CourseSchedule.semester == semester,
                CourseSchedule.class_name == class_name
            ).first() is not None
            return record

    def is_instructor_exist(self, semester: str, instructor: str) -> bool:
        with szh_info_sessionLocal() as session:
            record = session.query(CourseSchedule).filter(
                CourseSchedule.semester == semester,
                CourseSchedule.instructor == instructor
            ).first() is not None
            return record

    def get_course_department_and_instructor_by_semester_and_course_name_and_class_name(self, semester: str,
                                                                                        course_name: str,
                                                                                        class_name: str) -> Dict[
        str, str]:
        with szh_info_sessionLocal() as session:
            sql = text("""
                select course_department, instructor from course_schedule where semester=:semester 
                and course_name = :course_name and class_name = :class_name
            """)
            result = session.execute(sql, {"semester": semester, "course_name": course_name,
                                           "class_name": class_name}).mappings().first()
            if result:
                return {
                    "course_department": result["course_department"] or "",
                    "instructor": result["instructor"] or ""
                }
            else:
                return {
                    "course_department": "",
                    "instructor": ""
                }

    def import_course_schedule(self, records: List[CourseSchedule]):
        with szh_info_sessionLocal() as session:
            if not records:
                return
            session.add_all(records)
            session.commit()

    def get_instructor_by_semester_course_class(self, semester: str, course_name: str, class_name: str) -> str:
        with szh_info_sessionLocal() as session:
            instructor = session.query(CourseSchedule.instructor).filter(
                CourseSchedule.semester == semester,
                CourseSchedule.class_name == class_name,
                CourseSchedule.course_name == course_name
            ).scalar()
            return instructor if instructor is not None else ''

    def get_teacher_by_course_dept_and_semester(self, course_department: str, semester: str) -> List[str]:
        with szh_info_sessionLocal() as session:
            results = session.query(CourseSchedule.instructor).filter(
                CourseSchedule.semester == semester,
                CourseSchedule.course_department == course_department
            ).all()
            list_instructor = list(set(
                result[0] for result in results
                if result[0]
            ))
            return list_instructor

    def get_course_info(self, course_id: str, name: str, code: str, course_type: str, course_class: str,
                        course_dept: str,
                        course_hour: str, note: str, page: int, page_size: int) -> (List[Dict], int):
        with szh_info_sessionLocal() as session:
            query = session.query(CourseInfo)
            if course_id:
                query = query.filter(CourseInfo.id == int(course_id))
            if name:
                query = query.filter(CourseInfo.name.ilike(f'%{name.strip()}%'))
            if code:
                query = query.filter(CourseInfo.code.ilike(f'%{code.strip()}%'))
            if course_type:
                query = query.filter(CourseInfo.course_type.ilike(f'%{course_type.strip()}%'))
            if course_class:
                query = query.filter(CourseInfo.course_class.ilike(f'%{course_class.strip()}%'))
            if course_dept:
                query = query.filter(CourseInfo.course_depart.ilike(f'%{course_dept.strip()}%'))
            if course_hour:
                query = query.filter(CourseInfo.course_hour.ilike(f'%{course_hour.strip()}%'))
            if note:
                query = query.filter(CourseInfo.note.ilike(f'%{note}%'))
            total_count = query.count()

            if page != 0 and page_size != 0:
                offset = (page - 1) * page_size
                query = query.offset(offset).limit(page_size)
            result = query.all()
        return from_list_orm_to_list_dict(result, {}), total_count

    def update_course_info(self, course_id: int, name: str, code: str, course_type: str, course_class: str,
                           course_dept: str, course_hour: str, note: str):
        with szh_info_sessionLocal() as session:
            update_data = {}
            if name:
                update_data['name'] = name.strip()
            if code:
                update_data['code'] = code.strip()
            if course_type:
                update_data['course_type'] = course_type.strip()
            if course_class:
                update_data['course_class'] = course_class.strip()
            if course_dept:
                update_data['course_depart'] = course_dept.strip()
            if course_hour:
                update_data['course_hour'] = course_hour.strip()
            if note:
                update_data['note'] = note
            if not update_data:
                return
            session.query(CourseInfo).filter(CourseInfo.id == course_id).update(update_data)
            session.commit()

    def add_course_info(self, name: str, code: str, course_type: str, course_class: str,
                        course_dept: str, course_hour: str, note: str):
        with szh_info_sessionLocal() as session:
            session.add(CourseInfo(name=name, code=code, course_type=course_type, course_class=course_class,
                                   course_depart=course_dept, course_hour=course_hour, note=note))
            session.commit()

    def delete_course_info(self, course_ids: List[int]):
        with szh_info_sessionLocal() as session:
            session.query(CourseInfo).filter(CourseInfo.id.in_(course_ids)).delete()
            session.commit()


class DepartmentQuery:

    def get_department_info(self):
        with szh_info_sessionLocal() as session:
            result = session.query(DepartmentInfo).all()
            return from_list_orm_to_list_dict(result, {})

    def get_dept_name_by_id(self, dept_id: str) -> str:
        with szh_info_sessionLocal() as session:
            sql = text("""
                SELECT name as department_name
                FROM department_info
                WHERE id = :dept_id
            """)
            result = session.execute(sql, {"dept_id": dept_id}).scalar()
            return result if result is not None else ""


class MaterialQuery:
    def get_teaching_plan_submission_by_school_first_check(
            self,
            semester: str,
            week_sql: str
    ) -> List[Dict[str, Any]]:
        with szh_management_sessionLocal() as session:
            sql = text(f"""
                    SELECT
                        ca.instructor AS teacher_name,          
                        ca.class_name,                       
                        ca.course_name,                    
                        ca.course_department AS department_name,
                        ca.course_type,
                        ca.major as major_code,                          
                        weeks.n AS teaching_week,           
                        CASE
                            WHEN tm.material_id IS NOT NULL THEN '已提交'
                            ELSE '未提交'
                        END AS submission_status,           
                        tm.submit_time,                     
                        tm.tgp_check,
                        tm.tgp_check_desc,
                        tm.dept_check,
                        tm.dept_check_desc,
                        CASE 
                        WHEN tm.material_type IS NULL THEN 'teaching_plan'
                    END AS material_type         
                    FROM szh_info.department_info d
                    JOIN szh_info.course_schedule ca
                        ON ca.course_department = d.name 
                    JOIN (
                        {week_sql}   
                    ) weeks
                        ON weeks.n <= ca.teaching_week   
                    LEFT JOIN szh_management.teaching_material_submit tm
                        ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                        AND tm.course_name = ca.course_name
                        AND tm.class_name = ca.class_name
                        AND tm.semester = ca.semester
                        AND tm.week = weeks.n                   -- 匹配当前教学周
                        AND tm.material_type = 'teaching_plan'  -- 只查教案类型
                    WHERE ca.semester = :semester
#                         AND ca.grade NOT LIKE '%高职%'
                        AND ca.course_department NOT IN ('学生科')
                        AND (
                            ca.course_department = '教务科'
                            OR ca.course_department LIKE '%公共基础%' 
                            OR ca.course_type NOT LIKE '%C%'
                        )
                        AND ca.instructor IS NOT NULL;            -- 指定学期
            """)
            result = session.execute(sql, {"semester": semester})
            rows = result.mappings().all()
            return rows

    def get_teaching_plan_submission_by_dept_first_check(
            self,
            dept_id: str,
            semester: str,
            week_sql: str
    ) -> List[Dict[str, Any]]:
        with szh_management_sessionLocal() as session:
            sql = text(f"""
                SELECT 
                    ca.instructor AS teacher_name,
                    ca.class_name,
                    ca.course_name,
                    ca.course_department AS department_name,
                    ca.course_type,
                    ca.major as major_code,
                    weeks.n AS teaching_week,
                    CASE
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    CASE 
                        WHEN tm.material_type IS NULL THEN 'teaching_plan'
                    END AS material_type
                FROM szh_info.department_info d
                JOIN szh_info.course_schedule ca 
                    ON ca.course_department = d.name
                JOIN (
                    {week_sql}
                ) weeks 
                    ON weeks.n <= ca.teaching_week
                LEFT JOIN szh_management.teaching_material_submit tm 
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.course_name = ca.course_name
                    AND tm.class_name = ca.class_name
                    AND tm.semester = ca.semester
                    AND tm.week = weeks.n
                    AND tm.material_type = 'teaching_plan'
                WHERE d.id = :dept_id
                    AND ca.semester = :semester
#                     AND ca.grade NOT LIKE '%高职%'
                    AND ca.course_department NOT IN ('学生科')
                    AND (
                        ca.course_department = '教务科'
                        OR ca.course_department LIKE '%公共基础%' 
                        OR ca.course_type NOT LIKE '%C%'
                    )
                    AND ca.instructor IS NOT NULL
            """)
            result = session.execute(sql, {"dept_id": dept_id, "semester": semester})
            rows = result.mappings().all()
            return rows

    def get_teaching_plan_submission_by_tgp_first_check(
            self,
            tgp_name: str,
            semester: str,
            week_sql: str
    ) -> List[Dict[str, Any]]:
        """
        查询某教研组在第一次检查中的教案提交情况（按课程 + 周次展开）

        Args:
            tgp_name (str): 教研组ID
            semester (str): 学期

        Returns:
            List[Dict[str, Any]]: 每条记录包含字段：
                teacher_name, class_name, course_name, major_code,
                teaching_week, submission_status, submit_time,
                tgp_check, tgp_check_desc, dept_check, dept_check_desc
        """
        with szh_management_sessionLocal() as session:
            sql = text(f"""
                SELECT
                    u.name as teacher_name,
                    ca.class_name,
                    ca.course_name,
                    ca.course_department as department_name,
                    ca.course_type,
                    ca.major as major_code,
                    weeks.n AS teaching_week,
                    CASE
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    CASE
                        WHEN tm.material_type IS NULL THEN 'teaching_plan'
                    END AS material_type
                FROM szh_management.tgp_member tgm
                    JOIN szh_management.teaching_group_semester tgs
                        ON tgm.tgp_semester_id = tgs.id
                    JOIN szh_management.teacher_detail u
                        ON tgm.user_id = u.teacher_id
                    JOIN szh_management.teaching_group_type tgt
                        ON tgs.type_id = tgt.id
                    JOIN szh_info.semester_info s
                        ON tgs.semester_id = s.id
                    JOIN szh_info.department_info d
                        ON tgt.department_id = d.id
                JOIN szh_info.course_schedule ca
                    ON ca.instructor = u.name
                    AND s.name = ca.semester
                JOIN (
                    {week_sql}
                ) weeks
                    ON weeks.n <= ca.teaching_week
                LEFT JOIN szh_management.teaching_material_submit tm
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.course_name = ca.course_name
                    AND tm.class_name = ca.class_name
                    AND tm.semester = ca.semester
                    AND tm.week = weeks.n
                    AND tm.material_type = 'teaching_plan'
                WHERE tgt.name = :tgp_name
                    AND ca.semester = :semester
                      AND s.name = :semester
                    AND ca.grade NOT LIKE '%高职%'
                    AND ca.course_department NOT IN ('学生科')
                    AND (
                            ca.course_department LIKE '%公共基础%'
                            OR ca.course_type NOT LIKE '%C%'
                        )
                    AND ca.instructor IS NOT NULL
            """)
            result = session.execute(sql, {"tgp_name": tgp_name, "semester": semester})
            rows = result.mappings().all()
            return rows

    def get_submission_by_school(self, semester: str, material_type: str) -> List[
        Dict[str, Any]]:
        with szh_management_sessionLocal() as session:
            sql = text("""
                SELECT
                    ca.instructor AS teacher_name,
                    ca.class_name,
                    ca.course_name,
                    ca.course_department AS department_name,
                    ca.course_type,
                    ca.major as major_code,
                    CASE
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    CASE 
                        WHEN tm.week != -1 THEN tm.week
                    END AS teaching_week,
                    tm.submit_time,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    tm.material_type
                FROM szh_info.department_info d
                INNER JOIN szh_info.course_schedule ca ON ca.course_department = d.name
                LEFT JOIN szh_management.teaching_material_submit tm ON
                    ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.course_name = ca.course_name
                    AND tm.class_name = ca.class_name
                    AND tm.semester = ca.semester
                    AND tm.material_type = :materialType
                WHERE ca.semester = :semester
                    AND ca.grade NOT LIKE '%高职%'
                    AND ca.course_department NOT IN ('学生科')
                    AND (
                            ca.course_department LIKE '%公共基础%' 
                            OR ca.course_type NOT LIKE '%C%'
                        )
                    AND ca.instructor IS NOT NULL;
            """)
            result = session.execute(sql, {"materialType": material_type, "semester": semester})
            rows = result.mappings().all()
            return rows

    def get_teaching_plan_submission_by_dept(
            self,
            dept_id: str,
            semester: str
    ) -> List[Dict[str, Any]]:
        with szh_management_sessionLocal() as session:
            select_part = """
                SELECT 
                    ca.instructor as teacher_name,
                    ca.class_name,
                    ca.course_name,
                    ca.course_department as department_name,
                    ca.course_type,
                    ca.major as major_code,
                    weeks.n AS teaching_week,
                    CASE 
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.file_path,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    tm.ai_check_result1,
                    tm.ai_check_time1,
                    tm.ai_check_result2,
                    tm.ai_check_detail2,
                    tm.ai_check_time2,
                    tm.ai_check_result3,
                    tm.ai_check_detail3,
                    tm.ai_check_time3,
                    CASE 
                        WHEN tm.material_type IS NULL THEN 'teaching_plan'
                    END AS material_type
            """

            from_part = """
                FROM szh_info.course_schedule ca
                JOIN (
                    SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
                    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 
                    UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 
                    UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 
                    UNION SELECT 20
                ) weeks ON weeks.n <= ca.teaching_week
                LEFT JOIN szh_management.teaching_material_submit tm
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.course_name = ca.course_name
                    AND tm.class_name = ca.class_name
                    AND tm.semester = ca.semester
                    AND tm.week = weeks.n
                    AND tm.material_type = 'teaching_plan'
            """

            # 如果不是查询所有部门，修改FROM部分
            if dept_id != "all":
                from_part = """
                    FROM szh_info.department_info d 
                    JOIN szh_info.course_schedule ca ON ca.course_department = d.name
                    JOIN (
                        SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
                        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 
                        UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 
                        UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 
                        UNION SELECT 20
                    ) weeks ON weeks.n <= ca.teaching_week
                    LEFT JOIN szh_management.teaching_material_submit tm
                        ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                        AND tm.course_name = ca.course_name
                        AND tm.class_name = ca.class_name
                        AND tm.semester = ca.semester
                        AND tm.week = weeks.n
                        AND tm.material_type = 'teaching_plan'
                """

            # WHERE条件
            where_conditions = [
                "ca.semester = :semester",
                "ca.course_department NOT IN ('学生科')",
                "(ca.course_department = '教务科' OR ca.course_department LIKE '%公共基础%' OR ca.course_type NOT LIKE '%C%')",
                "ca.instructor IS NOT NULL"
            ]

            if dept_id != "all":
                where_conditions.insert(0, "d.id = :dept_id")
            # 构建完整SQL
            sql_text = select_part + from_part + " WHERE " + " AND ".join(where_conditions)
            sql = text(sql_text)
            # 执行查询
            params = {"semester": semester}
            if dept_id != "all":
                params["dept_id"] = dept_id

            result = session.execute(sql, params)
            rows = result.mappings().all()
            return rows

    def get_course_submission_by_dept(
            self,
            dept_id: str,
            semester: str,
            material_type: str
    ) -> List[Dict[str, Any]]:
        with szh_management_sessionLocal() as session:
            base_sql = """
                SELECT
                    ca.instructor AS teacher_name,
                    ca.class_name,
                    ca.course_name,
                    ca.course_department AS department_name,
                    ca.course_type,
                    ca.major as major_code,
                    CASE
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.file_path,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    tm.ai_check_result1,
                    tm.ai_check_time1,
                    tm.ai_check_result2,
                    tm.ai_check_detail2,
                    tm.ai_check_time2,
                    tm.ai_check_result3,
                    tm.ai_check_detail3,
                    tm.ai_check_time3,
                    CASE 
                        WHEN tm.material_type IS NULL THEN :material_type
                    END AS material_type
                FROM szh_info.department_info d
                JOIN szh_info.course_schedule ca ON ca.course_department = d.name
                LEFT JOIN szh_management.teaching_material_submit tm
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.course_name = ca.course_name
                    AND tm.class_name = ca.class_name
                    AND tm.semester = ca.semester
                    AND tm.material_type = :material_type
                WHERE 
                    d.id = :dept_id
                    AND ca.semester = :semester
                    AND ca.course_department NOT IN ('学生科')
                    AND (
                        ca.course_department = '教务科'
                        OR ca.course_department LIKE '%公共基础%' 
                        OR ca.course_type NOT LIKE '%C%'
                    )
                    AND ca.instructor IS NOT NULL
            """
            if material_type == 'training_plan':
                base_sql += """
                    AND ca.course_department NOT LIKE '%公共基础%'
                    AND ca.course_type NOT LIKE '%A%'
                """
            sql = text(base_sql)
            result = session.execute(
                sql,
                {"dept_id": dept_id, "semester": semester, "material_type": material_type}
            )
            return result.mappings().all()

    def get_submission_by_dept(
            self,
            dept_id: str,
            semester: str,
            material_type: str
    ) -> List[Dict[str, Any]]:
        """
        查询指定系部、学期和文件类型下的教学文件提交情况（按课程），
        严格对应原 Java/MyBatis SQL。

        Args:
            dept_id (str): 系部ID
            semester (str): 学期
            material_type (str): 文件类型（material_type）

        Returns:
            List[Dict]: 每条记录包含字段：
                        teacher_name, submission_status, submit_time,
                        tgp_check, tgp_check_desc, dept_check, dept_check_desc
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                SELECT 
                    u.name as teacher_name, 
                    ca.major as major_code,
                    ca.course_name,
                    ca.class_name,
                    ca.course_department as department_name,
                    ca.course_type,
                    CASE
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.file_path, 
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    tm.ai_check_result1,
                    tm.ai_check_time1,
                    tm.ai_check_result2,
                    tm.ai_check_detail2,
                    tm.ai_check_time2,
                    tm.ai_check_result3,
                    tm.ai_check_detail3,
                    tm.ai_check_time3,
                    CASE 
                        WHEN tm.material_type IS NULL THEN :material_type
                    END AS material_type
                FROM szh_info.department_info d
                JOIN szh_info.teacher_info u ON d.name = u.department
                JOIN szh_info.course_schedule ca ON u.name = ca.instructor
                LEFT JOIN szh_management.teaching_material_submit tm
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.semester = ca.semester
                    AND tm.material_type = :material_type
                WHERE d.id = :dept_id
                    AND ca.semester = :semester
                    # AND ca.grade NOT LIKE '%高职%'
                    AND ca.course_department NOT IN ('学生科')
                    AND (
                        ca.course_department = '教务科'
                        OR ca.course_department LIKE '%公共基础%' 
                        OR ca.course_type NOT LIKE '%C%'
                    )
                    AND ca.instructor IS NOT NULL;  
            """)
            result = session.execute(
                sql,
                {"dept_id": dept_id, "semester": semester, "material_type": material_type}
            )
            rows = result.mappings().all()
            return rows

    def get_teaching_plan_submission_by_tgp(
            self,
            tgp_name: str,
            semester: str
    ) -> List[Dict[str, Any]]:
        """
        查询指定教研组和学期下教师教学计划提交情况
        返回每位教师每周的提交状态和审核信息

        Args:
            tgp_name (str): 教研组ID
            semester (str): 学期

        Returns:
            List[Dict]: 每条记录包含字段：
                        teacher_name, class_name, course_name, teaching_week,
                        submission_status, submit_time,
                        tgp_check, tgp_check_desc, dept_check, dept_check_desc
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                SELECT 
                    u.name as teacher_name,
                    ca.class_name,
                    ca.course_name,
                    ca.course_department as department_name,
                    ca.course_type,
                    ca.major as major_code,
                    weeks.n AS teaching_week,
                    CASE 
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    CASE 
                        WHEN tm.material_type IS NULL THEN 'teaching_plan'
                    END AS material_type
                FROM szh_management.tgp_member tgm
                    JOIN szh_management.teaching_group_semester tgs 
                        ON tgm.tgp_semester_id = tgs.id
                    JOIN szh_management.teacher_detail u
                        ON tgm.user_id = u.teacher_id
                    JOIN szh_management.teaching_group_type tgt
                        ON tgs.type_id = tgt.id
                    JOIN szh_info.semester_info s
                        ON tgs.semester_id = s.id
                    JOIN szh_info.department_info d
                        ON tgt.department_id = d.id
                JOIN szh_info.course_schedule ca 
                    ON u.name = ca.instructor
                    AND s.name = ca.semester
                JOIN (
                    SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
                    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 
                    UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 
                    UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 
                    UNION SELECT 20
                ) weeks ON weeks.n <= ca.teaching_week
                LEFT JOIN szh_management.teaching_material_submit tm
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.course_name = ca.course_name
                    AND tm.class_name = ca.class_name
                    AND tm.semester = ca.semester
                    AND tm.week = weeks.n
                    AND tm.material_type = 'teaching_plan'
                WHERE
                    tgt.name = :tgp_name
                    AND ca.semester = :semester
                    AND s.name = :semester
                    AND ca.grade NOT LIKE '%高职%'
                    AND ca.course_department NOT IN ('学生科')
                    AND (
                            ca.course_department LIKE '%公共基础%' 
                            OR ca.course_type NOT LIKE '%C%'
                        )
                    AND ca.instructor IS NOT NULL
            """)
            result = session.execute(sql, {"tgp_name": tgp_name, "semester": semester})
            rows = result.mappings().all()
            return rows

    def get_course_submission_by_tgp(
            self,
            tgp_name: str,
            semester: str,
            material_type: str
    ) -> List[Dict[str, Any]]:

        base_sql = """
            SELECT 
                u.name as teacher_name,
                ca.class_name,
                ca.course_name,
                ca.course_department as department_name,
                ca.course_type,
                CASE
                    WHEN tm.material_id IS NOT NULL THEN '已提交'
                    ELSE '未提交'
                END AS submission_status,
                tm.submit_time,
                tm.tgp_check,
                tm.tgp_check_desc,
                tm.dept_check,
                tm.dept_check_desc,
                ca.major as major_code,
                CASE 
                    WHEN tm.material_type IS NULL THEN :material_type
                END AS material_type
            FROM szh_management.tgp_member tgm
            JOIN szh_management.teaching_group_semester tgs ON tgm.tgp_semester_id = tgs.id
            JOIN szh_management.teacher_detail u ON tgm.user_id = u.teacher_id
            JOIN szh_management.teaching_group_type tgt ON tgs.type_id = tgt.id
            JOIN szh_info.semester_info s ON tgs.semester_id = s.id
            JOIN szh_info.department_info d ON tgt.department_id = d.id
            JOIN szh_info.course_schedule ca ON u.name = ca.instructor AND s.name = ca.semester
            LEFT JOIN szh_management.teaching_material_submit tm
                ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                AND tm.course_name = ca.course_name
                AND tm.class_name = ca.class_name
                AND tm.semester = ca.semester
                AND tm.material_type = :material_type
            WHERE tgt.name = :tgp_name
              AND ca.semester = :semester
              AND s.name = :semester
              AND ca.grade NOT LIKE '%高职%'
              AND ca.course_department NOT IN ('学生科')
              AND (ca.course_department LIKE '%公共基础%' OR ca.course_type NOT LIKE '%C%')
              AND ca.instructor IS NOT NULL
        """
        # training_plan的特殊条件
        if material_type == "training_plan":
            base_sql += """
              AND ca.course_department NOT LIKE '%公共基础%'
              AND ca.course_type NOT LIKE '%A%'
            """

        with szh_management_sessionLocal() as session:
            result = session.execute(
                text(base_sql),
                {"tgp_name": tgp_name, "semester": semester, "material_type": material_type}
            )
            return result.mappings().all()

    def get_submission_by_tgp(
            self,
            tgp_name: str,
            semester: str,
            materialType: str
    ) -> List[Dict[str, Any]]:
        """
        查询没有课程的文件的提交情况（按教研组统计）。

        Args:
            tgp_name (str): 教研组ID
            semester (str): 学期
            materialType (str): 文件类型

        Returns:
            List[Dict]: 每条记录包含字段：
                        teacher_name, submission_status, submit_time,
                        tgp_check, dept_check,
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                SELECT 
                    u.name as teacher_name,
                    CASE
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    ca.course_department as department_name,
                    ca.class_name,
                    ca.course_name,
                    ca.course_type,
                    ca.major as major_code,
                    CASE 
                        WHEN tm.material_type IS NULL THEN :materialType
                    END AS material_type
                FROM szh_management.tgp_member tgm
                    JOIN szh_management.teaching_group_semester tgs 
                        ON tgm.tgp_semester_id = tgs.id
                    JOIN szh_management.teacher_detail u
                        ON tgm.user_id = u.teacher_id
                    JOIN szh_management.teaching_group_type tgt
                        ON tgs.type_id = tgt.id
                    JOIN szh_info.semester_info s
                        ON tgs.semester_id = s.id
                    JOIN szh_info.department_info d
                        ON tgt.department_id = d.id
                JOIN szh_info.course_schedule ca 
                    ON u.name = ca.instructor
                    AND s.name = ca.semester
                LEFT JOIN szh_management.teaching_material_submit tm
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.semester = ca.semester
                    AND tm.material_type = :materialType
                WHERE tgt.name = :tgp_name
                  AND ca.semester = :semester
                  AND s.name = :semester
                  AND ca.grade NOT LIKE '%高职%'
                 AND ca.course_department NOT IN ('学生科')
                  AND (
                        ca.course_department LIKE '%公共基础%' 
                        OR ca.course_type NOT LIKE '%C%'
                    )
                  AND ca.instructor IS NOT NULL
            """)
            result = session.execute(
                sql,
                {"tgp_name": tgp_name, "semester": semester, "materialType": materialType}
            )
            rows = result.mappings().all()
            return rows

    def get_teaching_plan_submission_by_teacher(
            self,
            instructor: str,
            semester: str,
            course_name: str,
            class_name: str
    ) -> List[Dict[str, Any]]:
        with szh_management_sessionLocal() as session:
            sql = text("""
                    SELECT 
                        tm.teacher_name,
                        weeks.n AS teaching_week,
                        CASE 
                            WHEN tm.material_id IS NOT NULL THEN '已提交'
                            ELSE '未提交'
                        END AS submission_status,
                        tm.submit_time,
                        tm.file_path,
                        tm.tgp_check,
                        tm.tgp_check_desc,
                        tm.dept_check,
                        tm.dept_check_desc,
                        ca.course_department as department_name,
                        ca.course_type,
                        ca.major as major_code,
                        CASE 
                        WHEN tm.material_type IS NULL THEN 'teaching_plan'
                    END AS material_type
                    FROM szh_info.course_schedule ca
                    JOIN (
                        SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
                        UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
                        UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15
                        UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
                    ) weeks ON weeks.n <= ca.teaching_week
                    LEFT JOIN szh_management.teaching_material_submit tm
                        ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                        AND tm.course_name = ca.course_name
                        AND tm.class_name = ca.class_name
                        AND tm.semester = ca.semester
                        AND tm.week = weeks.n
                        AND tm.material_type = 'teaching_plan'
                    WHERE
                        ca.instructor LIKE CONCAT('%', :instructor, '%')
                        AND ca.semester = :semester
                        AND ca.course_name = :course_name
                        AND ca.class_name = :class_name
                        # AND ca.grade NOT LIKE '%高职%'
                        AND ca.course_department NOT IN ('学生科')
                        AND (
                            ca.course_department = '教务科'
                            OR ca.course_department LIKE '%公共基础%' 
                            OR ca.course_type NOT LIKE '%C%'
                        )
                        AND ca.instructor IS NOT NULL;
                    """)
            result = session.execute(
                sql, {
                    "instructor": instructor,
                    "semester": semester,
                    "course_name": course_name,
                    "class_name": class_name,
                }
            )
            rows = result.mappings().all()
            return rows

    def get_course_submission_by_teacher(
            self,
            instructor: str,
            semester: str,
            course_name: str,
            class_name: str,
            material_type: str
    ) -> List[Dict[str, Any]]:

        base_sql = """
            SELECT 
                tm.teacher_name,
                tm.material_type,
                CASE
                    WHEN tm.material_id IS NOT NULL THEN '已提交'
                    ELSE '未提交'
                END AS submission_status,
                tm.submit_time,
                tm.file_path,
                tm.tgp_check,
                tm.tgp_check_desc,
                tm.dept_check,
                tm.dept_check_desc,
                ca.course_department as department_name,
                ca.course_type,
                ca.major as major_code,
                CASE 
                    WHEN tm.material_type IS NULL THEN :material_type
                END AS material_type
            FROM szh_info.course_schedule ca
            LEFT JOIN szh_management.teaching_material_submit tm
                ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                AND tm.course_name = ca.course_name
                AND tm.class_name = ca.class_name
                AND tm.semester = ca.semester
                AND tm.material_type = :material_type
            WHERE
                ca.instructor LIKE CONCAT('%', :instructor, '%')
                AND ca.semester = :semester
                AND ca.course_name = :course_name
                AND ca.class_name = :class_name
                AND ca.course_department NOT IN ('学生科')
                AND (
                    ca.course_department = '教务科'
                    OR ca.course_department LIKE '%公共基础%' 
                    OR ca.course_type NOT LIKE '%C%'
                )
                AND ca.instructor IS NOT NULL
        """

        if material_type == 'training_plan':
            base_sql += """
                AND ca.course_department NOT LIKE '%公共基础%'
                AND ca.course_type NOT LIKE '%A%'
            """

        with szh_management_sessionLocal() as session:
            result = session.execute(
                text(base_sql),
                {
                    "instructor": instructor,
                    "semester": semester,
                    "course_name": course_name,
                    "class_name": class_name,
                    "material_type": material_type
                }
            )
            return result.mappings().all()

    def get_submission_by_teacher(
            self,
            instructor: str,
            semester: str,
            course_name: str,
            class_name: str,
            material_type: str
    ) -> List[Dict[str, Any]]:

        with szh_management_sessionLocal() as session:
            sql = text("""
                SELECT 
                    tm.teacher_name,
                    tm.material_type,
                    CASE
                        WHEN tm.material_id IS NOT NULL THEN '已提交'
                        ELSE '未提交'
                    END AS submission_status,
                    tm.submit_time,
                    tm.file_path,
                    tm.tgp_check,
                    tm.tgp_check_desc,
                    tm.dept_check,
                    tm.dept_check_desc,
                    ca.course_type,
                    ca.course_department as department_name,
                    ca.major as major_code,
                    CASE 
                        WHEN tm.material_type IS NULL THEN :material_type
                    END AS material_type
                FROM szh_info.course_schedule ca
                LEFT JOIN szh_management.teaching_material_submit tm
                    ON ca.instructor LIKE CONCAT('%', tm.teacher_name, '%')
                    AND tm.semester = ca.semester
                    AND tm.material_type = :material_type
                WHERE
                    ca.instructor LIKE CONCAT('%', :instructor, '%')
                    AND ca.semester = :semester
                    AND ca.course_name = :course_name
                    AND ca.class_name = :class_name
                    # AND ca.grade NOT LIKE '%高职%'
                    AND ca.course_department NOT IN ('学生科')
                    AND (
                        ca.course_department = '教务科'
                        OR ca.course_department LIKE '%公共基础%' 
                        OR ca.course_type NOT LIKE '%C%'
                    )
                    AND ca.instructor IS NOT NULL
            """)
            result = session.execute(
                sql,
                {
                    "instructor": instructor,
                    "semester": semester,
                    "course_name": course_name,
                    "class_name": class_name,
                    "material_type": material_type
                }
            )
            return result.mappings().all()

    def get_all_submissions_by_teacher(self, semester: str, teacher_name: str, material_type: str):
        with szh_management_sessionLocal() as session:
            query = session.query(TeachingMaterialSubmit).filter(
                TeachingMaterialSubmit.semester == semester,
                TeachingMaterialSubmit.teacher_name == teacher_name,
                TeachingMaterialSubmit.material_type == material_type
            )
            result = query.all()
            return from_list_orm_to_list_dict(result, {})

    def update_submission(
            self,
            teacher_name: str = None,
            semester: str = None,
            course_name: str = None,
            class_name: str = None,
            week: int = None,
            material_type: str = None,
            file_path: str = None
    ) -> None:
        with szh_management_sessionLocal() as session:
            # 基础 SQL 语句
            base_sql = "UPDATE teaching_material_submit SET submit_time = NOW()"
            params = {}

            # 动态添加 SET 子句
            if file_path is not None:
                base_sql += ", file_path = :file_path"
                params["file_path"] = file_path

            if teacher_name:
                base_sql += ", teacher_name = :teacher_name"
                params["teacher_name"] = teacher_name
            # 动态构建 WHERE 条件
            where_conditions = []

            if semester:
                where_conditions.append("semester = :semester")
                params["semester"] = semester

            if course_name:
                where_conditions.append("course_name = :course_name")
                params["course_name"] = course_name

            if class_name:
                where_conditions.append("class_name = :class_name")
                params["class_name"] = class_name

            if week:
                where_conditions.append("week = :week")
                params["week"] = week

            if material_type:
                where_conditions.append("material_type = :material_type")
                params["material_type"] = material_type

            # 如果没有 WHERE 条件，可以选择抛出异常或更新所有记录
            if not where_conditions:
                raise ValueError("查询异常，SQL有问题")

            # 组合完整的 SQL
            full_sql = base_sql + " WHERE " + " AND ".join(where_conditions)

            session.execute(text(full_sql), params)
            session.commit()

    def submit(
            self,
            teacher_name: str,
            semester: str,
            course_name: str,
            course_department: str,
            class_name: str,
            week: int,
            material_type: str,
            file_path: str
    ) -> None:
        """
        插入一条新的教学材料记录
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                INSERT INTO teaching_material_submit
                (teacher_name, course_name, course_department, class_name, semester, week, material_type, file_path, submit_time)
                VALUES
                (:teacher_name, :course_name, :course_department, :class_name, :semester, :week, :material_type,
                 :file_path, :submit_time)
            """)
            session.execute(
                sql,
                {
                    "teacher_name": teacher_name,
                    "semester": semester,
                    "course_name": course_name,
                    "course_department": course_department,
                    "class_name": class_name,
                    "week": week,
                    "material_type": material_type,
                    "file_path": file_path,
                    "submit_time": datetime.now()
                }
            )
            session.commit()

    def get_submission_existence(
            self,
            teacher_name: str = None,
            semester: str = None,
            course_name: str = None,
            class_name: str = None,
            week: int = None,
            material_type: str = None
    ) -> bool:
        """
        判断指定条件的教学材料是否已提交（参数可为空）
        """
        with szh_management_sessionLocal() as session:
            # 基础查询
            query = session.query(TeachingMaterialSubmit)
            filters = []
            if teacher_name:
                filters.append(TeachingMaterialSubmit.teacher_name == teacher_name)
            if semester:
                filters.append(TeachingMaterialSubmit.semester == semester)
            if course_name:
                filters.append(TeachingMaterialSubmit.course_name == course_name)
            if class_name:
                filters.append(TeachingMaterialSubmit.class_name == class_name)
            if week is not None:  # 注意：week 可能是 0，所以用 is not None
                filters.append(TeachingMaterialSubmit.week == week)
            if material_type:
                filters.append(TeachingMaterialSubmit.material_type == material_type)
            if not filters:
                return False
            query = query.filter(*filters)
            # print("SQL:", query.statement.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True}))
            record_exists = query.first() is not None
            return record_exists

    def approve_material_by_dept(
            self,
            teacher_name: str,
            semester: str,
            course_name: str,
            class_name: str,
            week: int,
            material_type: str,
            check: bool,
            check_desc: str
    ) -> None:
        """
        部门审核教学材料，更新 dept_check 和 dept_check_desc
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                UPDATE teaching_material_submit
                SET dept_check = :check,dept_check_desc = :check_desc
                WHERE teacher_name = :teacher_name
                  AND semester = :semester
                  AND course_name = :course_name
                  AND class_name = :class_name
                  AND week = :week
                  AND material_type = :material_type
            """)
            session.execute(
                sql,
                {
                    "teacher_name": teacher_name,
                    "semester": semester,
                    "course_name": course_name,
                    "class_name": class_name,
                    "week": week,
                    "material_type": material_type,
                    "check": check,
                    "check_desc": check_desc
                }
            )

            session.commit()

    def approve_material_by_tgp(
            self,
            teacherName: str,
            semester: str,
            courseName: str,
            className: str,
            week: int,
            material_type: str,
            check: bool,
            check_desc: str
    ) -> None:
        """
        教研组审核教学材料，更新 tgp_check 和 tgp_check_desc

        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                UPDATE teaching_material_submit
                SET tgp_check = :check,tgp_check_desc = :check_desc
                WHERE teacher_name = :teacherName
                  AND semester = :semester
                  AND course_name = :courseName
                  AND class_name = :className
                  AND week = :week
                  AND material_type = :material_type
            """)
            session.execute(
                sql,
                {
                    "teacherName": teacherName,
                    "semester": semester,
                    "courseName": courseName,
                    "className": className,
                    "week": week,
                    "material_type": material_type,
                    "check": check,
                    "check_desc": check_desc
                }
            )
            session.commit()

    def approve_material_by_dept_for_course(self, teacher_name, semester, courseName,
                                            class_name, check, material_type):
        """
        更新某课程的院系审核结果
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                        UPDATE teaching_material_submit
                        SET dept_check = :check
                        WHERE teacher_name = :teacher_name
                          AND semester = :semester
                          AND course_name = :courseName
                          AND class_name = :class_name
                          AND material_type = :material_type
                    """)
            session.execute(sql, {
                "teacher_name": teacher_name,
                "semester": semester,
                "courseName": courseName,
                "class_name": class_name,
                "check": check,
                "material_type": material_type
            })
            session.commit()

    def approve_material_by_tgp_for_course(self, teacher_name, semester, course_name,
                                           class_name, check, material_type):
        """
        更新某课程的教务处审核结果
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                        UPDATE teaching_material_submit
                        SET tgp_check = :check
                        WHERE teacher_name = :teacher_name
                          AND semester = :semester
                          AND course_name = :course_name
                          AND class_name = :class_name
                          AND material_type = :material_type
                    """)
            session.execute(sql, {
                "teacher_name": teacher_name,
                "semester": semester,
                "course_name": course_name,
                "class_name": class_name,
                "check": check,
                "material_type": material_type
            })
            session.commit()

    def find_by_teacher_name_and_course_name_and_class_name_and_semester_and_week_and_material_type(
            self,
            teacher_name: str,
            course_name: str,
            class_name: str,
            semester: str,
            week: int,
            material_type: str
    ) -> Optional[TeachingMaterialSubmit]:
        """
        根据教师ID、课程ID、班级、学期、周次和材料类型查找单条记录
        """
        with szh_management_sessionLocal() as session:
            return (
                session.query(TeachingMaterialSubmit)
                .filter(
                    TeachingMaterialSubmit.teacher_name == teacher_name,
                    TeachingMaterialSubmit.course_name == course_name,
                    TeachingMaterialSubmit.class_name == class_name,
                    TeachingMaterialSubmit.semester == semester,
                    TeachingMaterialSubmit.week == week,
                    TeachingMaterialSubmit.material_type == material_type,
                )
                .one_or_none()
            )

    def save(self, material: TeachingMaterialSubmit) -> TeachingMaterialSubmit:
        """
        保存或更新一条TeachingMaterial记录
        """
        with szh_management_sessionLocal() as session:
            session.add(material)
            session.commit()
            session.refresh(material)  # 刷新对象状态
            return material

    def get_file_path(self, teacher_name: str, semester: str, course_name: str, class_name: str, week: int,
                      material_type: str):
        with szh_management_sessionLocal() as session:
            query = session.query(TeachingMaterialSubmit.file_path)
            filters = []
            if teacher_name:
                filters.append(TeachingMaterialSubmit.teacher_name == teacher_name)
            if semester:
                filters.append(TeachingMaterialSubmit.semester == semester)
            if course_name:
                filters.append(TeachingMaterialSubmit.course_name == course_name)
            if class_name:
                filters.append(TeachingMaterialSubmit.class_name == class_name)
            if week:
                filters.append(TeachingMaterialSubmit.week == week)
            if material_type:
                filters.append(TeachingMaterialSubmit.material_type == material_type)
            if filters:
                query = query.filter(*filters)
            return query.scalar()

    def get_material_by_file_path(self, file_path: str):
        with szh_management_sessionLocal() as session:
            return session.query(TeachingMaterialSubmit).filter(TeachingMaterialSubmit.file_path == file_path).first()

    def remove_teaching_material(self, teacher_name: str, semester: str, course_name: str, class_name: str, week: int,
                                 material_type: str):
        with szh_management_sessionLocal() as session:
            session.query(TeachingMaterialSubmit).filter(
                TeachingMaterialSubmit.teacher_name == teacher_name,
                TeachingMaterialSubmit.semester == semester,
                TeachingMaterialSubmit.course_name == course_name,
                TeachingMaterialSubmit.class_name == class_name,
                TeachingMaterialSubmit.week == week,
                TeachingMaterialSubmit.material_type == material_type
            ).delete()
            session.commit()

    def get_file_path_list_by_semester_and_dept_name_and_material_type_and_week(self, semester: str, dept_name: str,
                                                                                material_type: str, week: str) -> List[
        str]:
        """
        动态查询文件路径列表
        参数为None时表示不添加该过滤条件
        """
        with szh_management_sessionLocal() as session:
            # 构建基础查询
            query = session.query(TeachingMaterialSubmit.file_path)

            # 动态添加过滤条件
            filters = []
            if semester:
                filters.append(TeachingMaterialSubmit.semester == semester)
            if dept_name:
                filters.append(TeachingMaterialSubmit.course_department == dept_name)
            if material_type:
                filters.append(TeachingMaterialSubmit.material_type == material_type)
            if week is not None:
                filters.append(TeachingMaterialSubmit.week == int(week))
            if filters:
                query = query.filter(*filters)
            results = query.all()
            return [result[0] for result in results if result[0] is not None]

    def update_introspection_result(self, file_path: str, introspection_result: str):
        """
        更新文件路径对应的比较结果

        Args:
            file_path (str): 文件路径
            comparison_result (str): 比较结果
        """
        with szh_management_sessionLocal() as session:
            record = session.query(TeachingMaterialSubmit).filter_by(file_path=file_path).first()
            if record:
                # 使用查询到的submit_time更新ai_check_time1
                session.query(TeachingMaterialSubmit).filter_by(file_path=file_path).update({
                    "ai_check_result1": introspection_result,
                    "ai_check_time1": record.submit_time  # 使用原记录的submit_time
                })
                session.commit()

    def update_content_result(self, file_path: str, content_detail: str, content_result: str):
        """
        更新文件路径对应的比较结果

        Args:
            file_path (str): 文件路径
            comparison_result (str): 比较结果
        """
        with szh_management_sessionLocal() as session:
            record = session.query(TeachingMaterialSubmit).filter_by(file_path=file_path).first()

            if record:
                # 使用查询到的submit_time更新ai_check_time1
                session.query(TeachingMaterialSubmit).filter_by(file_path=file_path).update({
                    "ai_check_detail2": content_detail,
                    "ai_check_result2": content_result,
                    "ai_check_time2": record.submit_time  # 使用原记录的submit_time
                })
                session.commit()

    def update_comparison_result(self, file_path: str, comparison_detail: str, comparison_result: str):
        """
        更新文件路径对应的比较结果

        Args:
            file_path (str): 文件路径
            comparison_result (str): 比较结果
        """
        with szh_management_sessionLocal() as session:
            record = session.query(TeachingMaterialSubmit).filter_by(file_path=file_path).scalar()
            if record:
                # 使用查询到的submit_time更新ai_check_time1
                session.query(TeachingMaterialSubmit).filter_by(file_path=file_path).update({
                    "ai_check_detail3": comparison_detail,
                    "ai_check_result3": comparison_result,
                    "ai_check_time3": record.submit_time  # 使用原记录的submit_time
                })
                session.commit()


class UserQuery:

    def get_info(self, teacher_id: str) -> Optional[Dict[str, Any]]:
        """
        根据教师ID获取教师用户信息

        Args:
            teacher_id (str): 教师ID

        Returns:
            Optional[Dict[str, Any]]: 一条教师信息记录，如果不存在则返回 None
        """
        with szh_management_sessionLocal() as session:
            sql = text("""
                SELECT teacher_id as teacherId ,ti.name as teacherName,di.name as department, 
                di.id as departmentId, td.role
                FROM szh_management.teacher_detail ti
                join szh_management.teacher_data td on ti.user_id = td.user_id
                join szh_info.department_info di on td.department_id = di.id
                WHERE teacher_id = :teacher_id
            """)
            result = session.execute(sql, {"teacher_id": teacher_id})
            row = result.mappings().first()
            return dict(row) if row else None

    def get_belong(self, teacher_name: str, semester: str) -> Dict[str, Any]:
        with szh_management_sessionLocal() as session:
            sql = text("""
                SELECT 
                    ti.name as teacher_name,
                    td.id as department_id,
                    di.name as department_name
                FROM szh_management.teacher_detail ti
                JOIN szh_management.teacher_data td 
                on ti.user_id = td.user_id
                JOIN szh_info.department_info di 
                on td.department_id = di.id
                WHERE ti.name = :teacher_name
            """)
            result = session.execute(sql, {"teacher_name": teacher_name})
            row = result.mappings().first()
            return dict(row) if row else None

    def get_name_by_teacher_id(self, teacher_id: str) -> Optional[str]:
        """
        根据教师ID查询教师姓名

        Args:
            teacher_id (str): 教师ID

        Returns:
            Optional[str]: 教师姓名，如果不存在返回 None
        """
        with szh_management_sessionLocal() as session:
            record = (
                session.query(TeacherDetail.name)
                .filter(TeacherDetail.user_id == teacher_id)
                .first()
            )
            if record:
                return record.name
            return None

    def get_id_by_teacher_name(self, teacher_name: str) -> Optional[str]:
        """
        根据教师姓名获取教师ID
        """
        with szh_management_sessionLocal() as session:
            sql = text("select teacher_id from teacher_detail where name = :teacherName")
            result = session.execute(sql, {"teacherName": teacher_name}).fetchone()
            return result[0] if result else None

    def get_latest_semester(self) -> Optional[str]:
        """
        从数据库返回最新学期
        """
        with szh_info_sessionLocal() as session:
            sql = text("""
                SELECT semester
                FROM course_schedule
                WHERE semester IS NOT NULL
                ORDER BY semester DESC
                LIMIT 1
            """)
            result = session.execute(sql).scalar()  # 返回单个值
            return result

    def get_semester_id_by_name(self, name: str) -> Optional[int]:
        with szh_management_sessionLocal() as session:
            record = session.query(Semester.id).filter(
                Semester.name == name
            ).scalar()
            return record

    def get_teachers(self, teacher_names: List[str]) -> List[Dict[str, str]]:
        with szh_management_sessionLocal() as session:
            results = session.query(TeacherDetail.user_id, TeacherDetail.name).filter(
                func.trim(TeacherDetail.name).in_(teacher_names)
            ).all()
            return [{"name": result.name.strip(), "user_id": result.user_id.strip()} for result in results]

    def is_tgp_leader(self, semester: str, teacher_name: str) -> bool:
        with szh_info_sessionLocal() as session:
            count = session.query(TeachingGroupInfo).filter(TeachingGroupInfo.semester == semester,
                                                            TeachingGroupInfo.tgp_leader == teacher_name).count()
            return count > 0

    def get_teaching_group_info_by_tgp_leader(self, semester: str, teacher_name: str):
        with szh_info_sessionLocal() as session:
            result = session.query(TeachingGroupInfo).filter(TeachingGroupInfo.semester == semester,
                                                             TeachingGroupInfo.tgp_leader == teacher_name).all()
            return from_list_orm_to_list_dict(result, {})


classes_query = ClassesQuery()
course_query = CourseQuery()
department_query = DepartmentQuery()
material_query = MaterialQuery()
user_query = UserQuery()
